/*
 * Copyright (c) 2023 Oracle and/or its affiliates.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package io.helidon.integrations.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.h2.jdbcx.JdbcDataSource;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.CoreMatchers.is;

final class TestH2IsolationLevelCases {

    private DataSource ds;

    private TestH2IsolationLevelCases() {
        super();
    }

    @BeforeEach
    final void initializeDataSource() throws SQLException {
        final JdbcDataSource ds = new JdbcDataSource();
        ds.setURL("jdbc:h2:mem:testH2IsolationLevelCases;INIT=SET DB_CLOSE_DELAY=-1");
        ds.setUser("sa");
        ds.setPassword("sa");
        this.ds = ds;
    }

    @AfterEach
    final void closeDataSource() throws SQLException {
        try (final Connection c = this.ds.getConnection();
             final Statement s = c.createStatement();) {
            s.execute("SHUTDOWN");
        }
    }

    @Test
    final void testGeneratedKeysAreVisibleWithinATransactionWithReadCommittedIsolationLevel() throws SQLException {
        try (final Connection c = this.ds.getConnection()) {
            assertThat(c.getTransactionIsolation(), is(Connection.TRANSACTION_READ_COMMITTED));
            c.setAutoCommit(false);
            assertThat(c.getAutoCommit(), is(false));

            Statement s = c.createStatement();
            s.executeUpdate("""
                            CREATE TABLE TRAINER (ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
                                                  AGE INTEGER NOT NULL,
                                                  NAME VARCHAR(255),
                                                  PRIMARY KEY (ID));
                            """);
            s.close();

            s = c.createStatement();
            s.executeUpdate("""
                            CREATE TABLE POKEMON (ID INTEGER NOT NULL,
                                                  CP INTEGER NOT NULL,
                                                  NAME VARCHAR(255),
                                                  TRAINER_ID INTEGER,
                                                  PRIMARY KEY (ID));
                            """);
            s.close();

            s = c.createStatement();
            s.executeUpdate("""
                            ALTER TABLE IF EXISTS POKEMON
                            ADD CONSTRAINT POKEMON_TO_TRAINER
                            FOREIGN KEY (TRAINER_ID) REFERENCES TRAINER;
                            """);
            s.close();

            PreparedStatement ps =
                c.prepareStatement("""
                                   INSERT INTO TRAINER (ID, AGE, NAME)
                                   VALUES (DEFAULT, ?, ?);
                                   """, Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1, 10);
            ps.setString(2, "Ash Ketchum");
            ps.executeUpdate();

            ResultSet keys = ps.getGeneratedKeys();
            keys.next();
            int id = keys.getInt(1);
            assertThat(id, is(1));
            keys.close();

            ps.close();

            ps =
                c.prepareStatement("""
                                   INSERT INTO POKEMON (CP, NAME, TRAINER_ID, ID)
                                   VALUES (?, ?, ?, ?);
                                   """);
            ps.setInt(1, 252);
            ps.setString(2, "Pikachu");
            ps.setInt(3, id);
            ps.setInt(4, 1);
            ps.executeUpdate();
            ps.close();

            c.commit();
        }
    }

}
